package main

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

const (
	queryTimeout = 3 * time.Second
)

var (
	dbConn_ *sql.DB
)

func initMysql() {
	mysqlConfig := getAppConfig().Mysql
	dsn := fmt.Sprintf("%v:%v@tcp(%v)/%s?%s", mysqlConfig.Username, mysqlConfig.Password, mysqlConfig.Address, mysqlConfig.Database, mysqlConfig.Params)
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		fatalf("sql.Open: %v\n", err)
	}
	dbConn_ = db
	if mysqlConfig.TestConn {
		testOk := false
		for range 3 {
			ctx, cancel := context.WithTimeout(context.Background(), time.Second*5)
			defer cancel()
			_, err := db.QueryContext(ctx, "SELECT 1")
			if err != nil {
				db.Close()
				fmt.Fprintf(os.Stderr, "connect mysql: %v\n", err)
				continue
			}
			testOk = true
			break
		}
		if !testOk {
			fatalf("cannot connect to mysql\n")
		}
	}
}

func getDbConn() *sql.DB {
	if dbConn_ == nil {
		initMysql()
	}
	return dbConn_
}

func getTableComment(database string, tableName string) (string, error) {
	const sqlQueryComment = `select TABLE_COMMENT from information_schema.TABLES where table_schema = ? and TABLE_NAME = ? limit 1`
	ctx, cancel := context.WithTimeout(context.Background(), queryTimeout)
	defer cancel()
	row := getDbConn().QueryRowContext(ctx, sqlQueryComment, database, tableName)
	if row.Err() != nil {
		return "", row.Err()
	}
	var comment string
	err := row.Scan(&comment)
	if err != nil && err != sql.ErrNoRows {
		return "", err
	}
	return comment, nil
}

type TableColumn struct {
	columnName    string
	dataType      string
	columnComment string
	columnDefault sql.NullString

	isNullable       bool
	isPrimaryKey     bool
	isUniqueKey      bool
	hasAutoIncrement bool
	hasOnUpdate      bool
}

func getTableColumns(database string, tableName string) ([]*TableColumn, error) {
	const sqlQueryColumns = `select COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_COMMENT, COLUMN_KEY, COLUMN_DEFAULT, EXTRA from information_schema.COLUMNS where table_schema = ? and TABLE_NAME = ? order by ORDINAL_POSITION`

	ctx, cancel := context.WithTimeout(context.Background(), queryTimeout)
	defer cancel()
	rows, err := getDbConn().QueryContext(ctx, sqlQueryColumns, database, tableName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var columns []*TableColumn
	for rows.Next() {
		var (
			isNullable string
			columnKey  string
			extra      string
		)
		c := &TableColumn{}
		err := rows.Scan(&c.columnName, &c.dataType, &isNullable, &c.columnComment, &columnKey, &c.columnDefault, &extra)
		if err != nil {
			return nil, err
		}

		if isNullable == "YES" {
			c.isNullable = true
		}

		switch columnKey {
		case "PRI":
			c.isPrimaryKey = true
		case "UNI":
			c.isUniqueKey = true
		}

		extra = strings.ToLower(extra)
		if extra == "auto_increment" {
			c.hasAutoIncrement = true
		} else if strings.Contains(extra, "on update ") {
			c.hasOnUpdate = true
		}
		columns = append(columns, c)
	}
	return columns, nil
}

func showTables(database string, prefix string) ([]string, error) {
	const sqlShowTables = `select table_name from information_schema.tables where table_schema = ? and table_name like ?`
	ctx, cancel := context.WithTimeout(context.Background(), queryTimeout)
	defer cancel()
	rows, err := getDbConn().QueryContext(ctx, sqlShowTables, database, prefix+"%")
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var tbls []string
	for rows.Next() {
		var tbl string
		err := rows.Scan(&tbl)
		if err != nil {
			return nil, err
		}
		tbls = append(tbls, tbl)
	}
	return tbls, nil
}
